import os
import sys

db_ip = 'localhost'
db_port = '5432'
db_user = 'postgres'
db_password = 'wenchao111'
database_name = 'futures_data'
interval = 500


# try:
#     db_ip = input('请输入数据库ip')
#     db_port = input('请输入数据库端口号')
#     db_user = input('请输入数据库用户名')
#     db_password = input('请输入数据库密码')
#     database_name = input('请输入数据库名称')
#     interval = int(input('请输入定时爬取的时间间隔,单位：秒'))
# except:
#     print("程序出现错误")
#     sys.exit()


def check_lib():
    print('正在安装需要的三方库...')
    # 添加需要安装的扩展包名称进去
    # psycopg2 支持PostgreSql数据库
    libs = {"requests", 'pymysql', 'sqlalchemy==1.4.36', 'schedule', 'psycopg2'}
    try:
        for lib in libs:
            os.system("pip install " + lib)
            print("{} Install successful".format(lib))
    except:
        print("install failed")
        sys.exit()
    print('三方库安装完成')


# 检查三方库
check_lib()

# 初始化数据库
import pymysql
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, BigInteger, DATETIME, Integer, TIMESTAMP

# 连接mysql数据库需要导入pymysql模块
pymysql.install_as_MySQLdb()

# 配置数据库地址：数据库类型+数据库驱动名称://用户名:密码@机器地址:端口号/数据库名
# engine = create_engine(f"mysql+pymysql://{db_user}:{db_password}@{db_ip}:{db_port}/{database_name}", encoding='utf-8')
engine = create_engine(f"postgresql+psycopg2://{db_user}:{db_password}@{db_ip}:{db_port}/{database_name}")
# 把当前的引擎绑定给这个会话；
# autocommit：是否自动提交 autoflush：是否自动刷新并加载数据库 bind：绑定数据库引擎
Session = sessionmaker(bind=engine)

# declarative_base类维持了一个从类到表的关系，通常一个应用使用一个Base实例，所有实体类都应该继承此类对象
Base = declarative_base()


class DataTable(Base):
    __tablename__ = "DataTable"
    id = Column(BigInteger, primary_key=True, autoincrement=True)
    VARNAME = Column(String(10), nullable=False, comment="类别")
    REGNAME = Column(String(10), nullable=False, comment="地区")
    WHABBRNAME = Column(String(20), nullable=False, comment="仓库")
    WRTWGHTS = Column(Integer, nullable=False, comment="期货", default=0)
    WRTCHANGE = Column(Integer, nullable=False, comment="增减", default=0)
    # update_date = Column(DATETIME, nullable=False, comment="刷新时间")
    update_date = Column(TIMESTAMP, nullable=False, comment="刷新时间")


# 在数据库中生成表
Base.metadata.create_all(bind=engine)

import json
import datetime

import requests

headers = {
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 11_1_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.96 Safari/537.36',
    'Referer': 'https://www.shfe.com.cn/statements/dataview.html?paramid=dailystock'
}

session = requests.session()
session.headers = headers


def get_html(url):
    html = session.get(url, headers=headers)
    if html.status_code == 200:
        get_real_data(0)
    else:
        print("请求失败，errorCode=", html.status_code)


def get_real_data(offset):
    # 获取今天的日期
    today = datetime.date.today()
    oneday = datetime.timedelta(days=offset)
    yesterday = today - oneday
    date_str = yesterday.strftime('%Y%m%d')

    html = session.get(f"https://www.shfe.com.cn/data/dailydata/{date_str}dailystock.dat", headers=headers)
    if html.status_code == 200:
        parse_data(html.text, yesterday.strftime('%Y-%m-%d'))
    else:
        print(f"请求失败，errorCode={html.status_code}")
        if html.status_code == 404:
            print('尝试获取前一天的数据')
            offset += 1
            get_real_data(offset)


def parse_data(json_str, date_str):
    print(json_str)
    data = json.loads(json_str)
    data_list = data['o_cursor']
    update_date_str = data['update_date']
    format_str = "%Y%m%d %H:%M:%S"
    update_date = datetime.datetime.strptime(update_date_str, format_str)

    db_session = Session()

    date_start = date_str + ' 00:00:00'
    date_end = date_str + ' 23:59:59'

    # 获取今天的的数据，没有就插入，如果已经有今天的数据判断更新时间，如果更新时间不一致就更新数据
    count = db_session.query(DataTable).filter(DataTable.update_date.between(date_start, date_end)).count()
    if count == 0:
        print('插入新的数据')
        for item in data_list:
            VARNAME = item['VARNAME'].split('$$')[0]
            REGNAME = item['REGNAME'].split('$$')[0]
            WHABBRNAME = item['WHABBRNAME'].split('$$')[0]
            WRTWGHTS = item['WRTWGHTS']
            WRTCHANGE = item['WRTCHANGE']
            if WHABBRNAME in ['合计', '保税商品总计', '完税商品总计', '总计']:
                continue
            print(f'{VARNAME},{REGNAME},{WHABBRNAME},{WRTWGHTS},{WRTCHANGE}')

            data = DataTable()
            data.VARNAME = VARNAME
            data.REGNAME = REGNAME
            data.WHABBRNAME = WHABBRNAME
            data.WRTWGHTS = WRTWGHTS
            data.WRTCHANGE = WRTCHANGE
            data.update_date = update_date
            db_session.add(data)
            db_session.commit()
    else:
        first_data = db_session.query(DataTable).filter(DataTable.update_date.between(date_start, date_end)).first()
        if first_data.update_date == update_date:
            print('数据已经是最新数据，无需更新')
        else:
            print('更新数据')
            for item in data_list:
                VARNAME = item['VARNAME'].split('$$')[0]
                REGNAME = item['REGNAME'].split('$$')[0]
                WHABBRNAME = item['WHABBRNAME'].split('$$')[0]
                WRTWGHTS = item['WRTWGHTS']
                WRTCHANGE = item['WRTCHANGE']
                if WHABBRNAME in ['合计', '保税商品总计', '完税商品总计', '总计']:
                    continue
                print(f'{VARNAME},{REGNAME},{WHABBRNAME},{WRTWGHTS},{WRTCHANGE}')

                one_data = db_session.query(DataTable).filter(DataTable.VARNAME == VARNAME).filter(
                    DataTable.REGNAME == REGNAME).filter(DataTable.WHABBRNAME == WHABBRNAME).first()

                one_data.WRTWGHTS = WRTWGHTS
                one_data.WRTCHANGE = WRTCHANGE
                one_data.update_date = update_date
                db_session.commit()


import schedule
import time


def work():
    print('正在为您爬取数据...')
    get_html("https://www.shfe.com.cn/statements/dataview.html?paramid=dailystock")


print('一分钟执行一次任务')
# 先执行一次
work()

schedule.every(interval).seconds.do(work)
while True:
    schedule.run_pending()
    time.sleep(1)
